﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Data.OleDb;
using EKETEAM.FrameWork;
using LitJson;

public partial class Plugins_ExcelToJson : System.Web.UI.Page
{
    string path = eParameters.QueryString("path");
    protected void Page_Load(object sender, EventArgs e)
    {
        if (path.ToLower().IndexOf("upload") == -1) eResult.Error("路径有误!");
        path = path.Substring(path.ToLower().IndexOf("upload/"));
        eFileInfo fi = new eFileInfo(path);
        string tagfolder = fi.Path;
        tagfolder += fi.Name + fi.fileExtension;

        string pathname = Server.MapPath("~/" + tagfolder);
        if (!File.Exists(pathname)) eResult.Error("文件不存在或已被删除!");

        OleDbConnection Oleconn;
        switch (fi.fileExtension)
        {
            case ".xlsx":
                Oleconn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pathname + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1;\";");
                break;
            case ".xls":
                if (IsText(pathname))
                {
                    Oleconn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathname + ";Extended Properties=\"HTML Import;HDR=Yes;\"");
                }
                else
                {
                    Oleconn = new OleDbConnection("provider=microsoft.jet.oledb.4.0;Excel 8.0;ReadOnly=True;HDR=YES;IMEX=1;database=" + pathname + ";");
                }
                break;
            case ".csv":
                Oleconn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"" + fi.Path + "\";Extended Properties=\"text;HDR=Yes;FMT=Delimited;\"");
                break;
            default:
                Oleconn = new OleDbConnection();
                break;
        }

        try
        {
            Oleconn.Open();
        }
        catch
        {
            eResult.Error("文件格式有误!");
        }
        DataTable dt = Oleconn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        if (dt.Rows.Count == 0) eResult.Error("文件有误!");
        DataRow dr = dt.Rows[0];
        DataTable tb = new DataTable();
        OleDbDataAdapter sda = new OleDbDataAdapter("Select * From [" + dr["TABLE_NAME"].ToString() + "]", Oleconn);
        sda.Fill(tb);
        //eBase.PrintDataTable(tb);

        //eBase.Write(tb.toJSON());

        Oleconn.Close();
        JsonData json = new JsonData();
        json["success"]= "1";
        json["errcode"] = "1";
        json["message"] = "请求成功!";
        json["data"] = tb.toJSONData();


        eResult.WriteJson(json);


        //eBase.Writeln(pathname);

    }
    public bool IsText(string filename)
    {
        if (!File.Exists(filename)) return false;
        System.IO.FileStream fs = new System.IO.FileStream(filename, System.IO.FileMode.Open, System.IO.FileAccess.Read);
        System.IO.BinaryReader br = new System.IO.BinaryReader(fs);
        Byte[] buffer = br.ReadBytes(2);
        br.Close();
        br.Dispose();
        fs.Close();
        fs.Dispose();

        if (buffer[0] == 60 || buffer[1] == 104) //utf-8
        {
            return true;
        }
        if (buffer[0] == 64 || buffer[0] == 49) //ANSI
        {
            return true;
        }
        if (buffer[0] == 208 && buffer[1] == 213) return true; //CSV

        if (buffer[0] >= 0xEF)
        {
            if (buffer[0] == 0xEF && buffer[1] == 0xBB)
            {
                return true;
            }
            else if (buffer[0] == 0xFE && buffer[1] == 0xFF)
            {
                return true;
            }
            else if (buffer[0] == 0xFF && buffer[1] == 0xFE)
            {
                return true;
            }
            else
            {
                return true;
            }
        }
        else
        {
            return false;
        }
    }
}